
ALTER PROC sp_create_update_diebank_receive_lot (
 
 @PROCESS_STEP_ID 	INT,	
 @FAB_VENDOR_NAME 	VARCHAR(50),
 @VENDOR_NAME 		VARCHAR(50),
 @FAB_LOT_NUMBER	VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50),
 @QTY_IN 			INT,
 @USER_NAME         VARCHAR(50),
 @PROCESS_TYPE      VARCHAR(50),
 @LOT_ID			INT ,
 @SHIP_LOT_ID		INT,
 @CHECKED_WAFERIDS VARCHAR(100),
 @UNCHECKED_WAFERIDS VARCHAR(100),
 @REMAIN_WAFER_QTY	INT
 )
 
 AS
 BEGIN
  	
 	DECLARE @LOCATION_ID INT
 	DECLARE @DIE_BANK_LOT_ID INT
	DECLARE @DATE_IN DATETIME
 	DECLARE @LOT_CATEGORY VARCHAR(50)
	DECLARE @LOT_NUMBER_AS_INT INT
	DECLARE @VENDOR_LOTNO INT
	DECLARE @LOT_NUMBER_AS_STR VARCHAR(50)
	DECLARE @LOT_STATUS   VARCHAR(50)
	DECLARE @GENERATE_LOTNO INT

	--setting lot category is production, when category is null
	set @LOT_CATEGORY = 'PROD'
	SET @DATE_IN	= CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
	
 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'DIE BANK' AND L_VENDOR_NAME = @VENDOR_NAME
 	
 	SELECT @DIE_BANK_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

 	IF @DIE_BANK_LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT, USER_NAME) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, null, 'Running', CURRENT_TIMESTAMP,0, @lot_Category, 0, @USER_NAME)
		SELECT @DIE_BANK_LOT_ID = @@IDENTITY
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP) VALUES (@DIE_BANK_LOT_ID, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP )		
	END
	ELSE
	BEGIN
		SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @DIE_BANK_LOT_ID
		IF @LOT_STATUS = 'Running' 
		BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', QTY_IN = QTY_IN + @QTY_IN, LOT_CATEGORY = @lot_Category, LOT_DROP_OUT = 0 WHERE LOT_ID = @DIE_BANK_LOT_ID
			UPDATE INVENTORY_LOT SET IL_QTY_IN = IL_QTY_IN+ @QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @DIE_BANK_LOT_ID
		END
		ELSE
		BEGIN
			 UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', QTY_IN = @QTY_IN, LOT_CATEGORY = @lot_Category, LOT_DROP_OUT = 0 WHERE LOT_ID = @DIE_BANK_LOT_ID
			 UPDATE INVENTORY_LOT SET IL_QTY_IN = @QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @DIE_BANK_LOT_ID	
		END
	END

	DECLARE @SO_NO VARCHAR(50), @PO_NO	VARCHAR(50), @INVOICE VARCHAR(50), @LC INT, @PKG VARCHAR(50), @NEXT_LOC VARCHAR(50) 
	DECLARE @FLT_NO VARCHAR(50),@MAWB VARCHAR(50),@HAWB VARCHAR(50)
	DECLARE @SHIP_DATE DATETIME, @ETD DATETIME,@ETA DATETIME
 	

	IF @REMAIN_WAFER_QTY > 0
	BEGIN
			IF @PROCESS_TYPE = 'FAB' OR @PROCESS_TYPE = 'FAB OPTION'
			BEGIN
				
				UPDATE SHIPPED_LOT SET SHIP_QTY = SHIP_QTY-@REMAIN_WAFER_QTY, STATUS = 'Close', SHIP_STATUS = 1, Close_Date = CURRENT_TIMESTAMP , WAFER_ID = @CHECKED_WAFERIDS WHERE SHIPPEDLOT_ID = @SHIP_LOT_ID
				
				SELECT @SHIP_DATE = SHIP_DATE, @SO_NO = SL_Customer_SO, @PO_NO = SL_Customer_PO, @INVOICE= SL_Invoice_Number, @LC=SL_Lead_Count, @PKG = SL_Package, @NEXT_LOC = SL_NextLocation, @MAWB = SL_MAWB_NUMBER, @HAWB = SL_HAWB_NUMBER, @FLT_NO = SL_Flight_Number,@ETD=SL_ETD, @ETA = SL_ETA FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @SHIP_LOT_ID
				
				INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, SHIP_QTY, TIMESTAMP, STATUS, SHIP_STATUS, SL_NEXT_PROCESS, SHIP_DROP_OUT, SL_CUSTOMER_SO, SL_CUSTOMER_PO, SL_INVOICE_NUMBER, SL_LEAD_COUNT, SL_PACKAGE, SL_NEXTLOCATION, SL_MAWB_NUMBER, SL_HAWB_NUMBER, SL_FLIGHT_NUMBER, SL_ETD, SL_ETA, WAFER_ID ) 
					VALUES( @LOT_ID, @SHIP_DATE, @REMAIN_WAFER_QTY, CURRENT_TIMESTAMP, 'TRANSIT', 0, 'DIE BANK', 0, @SO_NO, @PO_NO, @INVOICE, @LC, @PKG, @NEXT_LOC, @MAWB, @HAWB, @FLT_NO, @ETD, @ETA,@UNCHECKED_WAFERIDS  )
			
			END
	END
	ELSE
	BEGIN
	 	 UPDATE SHIPPED_LOT SET STATUS = 'Close', SHIP_STATUS = 1 , Close_Date = CURRENT_TIMESTAMP WHERE SHIPPEDLOT_ID = @SHIP_LOT_ID
	END

	SELECT @GENERATE_LOTNO = Generate_LotNo FROM Lot_Number_Rule_Table
	
	IF @GENERATE_LOTNO = 0 AND (@PROCESS_TYPE = 'FAB' OR @PROCESS_TYPE = 'FAB OPTION')
	BEGIN
   		SET @LOT_NUMBER_AS_INT = CONVERT(INT,SUBSTRING(@lot_number, len(@lot_number)-2, len(@lot_number) ) )
		SELECT @VENDOR_LOTNO = 	CONVERT(INT,ISNULL(V_LOTNO, '001') ) FROM VENDOR WHERE V_NAME = @FAB_VENDOR_NAME
		
		IF @LOT_NUMBER_AS_INT >= @VENDOR_LOTNO
		BEGIN
				if ( @LOT_NUMBER_AS_INT < 9 )
				BEGIN
					SET @LOT_NUMBER_AS_STR = '00'+ CONVERT(VARCHAR,(@LOT_NUMBER_AS_INT + 1) )
				END
				ELSE IF (@LOT_NUMBER_AS_INT < 99 )
				BEGIN
					SET @LOT_NUMBER_AS_STR = '0'+ CONVERT(VARCHAR,(@LOT_NUMBER_AS_INT + 1) )
				END
				ELSE IF (@LOT_NUMBER_AS_INT < 999)
				BEGIN
					SET @LOT_NUMBER_AS_STR = CONVERT(VARCHAR,(@LOT_NUMBER_AS_INT + 1) )
				END
				/*ELSE IF (@LOT_NUMBER_AS_INT < 9999)
				BEGIN
					SET @LOT_NUMBER_AS_STR = CONVERT(VARCHAR,(@LOT_NUMBER_AS_INT + 1) )
				END*/

				UPDATE VENDOR SET V_LOTNO = @LOT_NUMBER_AS_STR WHERE V_NAME = @FAB_VENDOR_NAME
		END
	END

 END
